﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using DataModels.Common;
using DataModels;
using GenericTools;
using GenericTools.Models;

namespace Reporting
{
    public class AutoReminder
    {
        #region Constants
        private const string REPORT_TEMPLATE_REPORT_ID_COLUMN_NAME = "ReportId";
        private const string REPORT_TEMPLATE_MESSAGE_TEMPLATE_COLUMN_NAME = "MessageTemplate";
        private const string REPORT_TEMPLATE_ENABLE_AUTOREMINDER_COLUMN_NAME = "EnableAutoReminder";
        private const string REPORT_TEMPLATE_REPORT_NAME_COLUMN_NAME = "ReportName";
        private const string REPORT_TEMPLATE_REPORT_VERSION_COLUMN_NAME = "RepVersion";

        private const string DISABLED_REPORT_TEMPLATES_FILTER_EXPRESSION = "EnableAutoReminder = 'FALSE'";
        private const string AUTOREMINDER_FACILITY_CODE_COLUMN_NAME = "FacilityCode";

        public const int MINIMUM_RECIPIENTS_FOR_LAST_REPORT_SENDER = 1;
        private const int UI_PRESET_TWO_REMINDING_ATTEMPTS = 2;
        private const int AUTO_REMINDER_TARGET_MONTHS_BACK = -1;

        private const int AUTO_REMINDER_WEEKLY_DAYS_GAP = 6;
        private const int AUTO_REMINDER_MONTHLY_DAYS_GAP = 25;

        public const string MESSAGE_TEMPLATE_PLACEHOLDER_YEAR = "{YEAR}";
        public const string MESSAGE_TEMPLATE_PLACEHOLDER_MONTH = "{MONTH}";
        public const string MESSAGE_TEMPLATE_PLACEHOLDER_REPORTS = "{REPORTS}"; 
        public const string MESSAGE_TEMPLATE_PLACEHOLDER_REPORT = "{REPORT}";
        #endregion

        private AutoReminderConfiguration _Configuration;
        public AutoReminderConfiguration Configuration
        {
            get
            {
                return _Configuration;
            }
            set
            {
                _Configuration = value;
            }
        }

        private int _DaysBeforeReminding;
        public int DaysBeforeRemining
        {
            get { return _DaysBeforeReminding; }
            set { _DaysBeforeReminding = value; }
        }

        private string _AllReportsMessageTemplate;
        public string AllReportsMessageTemplate
        {
            get { return _AllReportsMessageTemplate; }
            set { _AllReportsMessageTemplate = value; }
        }

        private DataTable _reportMessageTemplates = null;
        private Dictionary<string, Dictionary<int, string>> _facilityMissingReports = null;

        private int _targetYear;
        private int _targetMonth;
        private string _targetYearText;
        private string _targetMonthText;

        private int _oneReminderPerFacilityLimit;
        private string _settingsLastSaveDate;

        private int _totalMissingReports;
        private int _totalSentMessages;
        private TimeSpan _timeTakenForSendingReminders;

        public int TotalMissingReports
        {
            get { return _totalMissingReports; }
        }

        public int TotalSentMessages
        {
            get { return _totalSentMessages; }
        }

        public TimeSpan TimeTakenForSendingReminders
        {
            get { return _timeTakenForSendingReminders; }
        }

        private void Initialize()
        {
            string setting = Utilities.GetSetting(AutoReminderSettings.AUTO_REMINDER_BASIC_INFO);
            if (string.IsNullOrEmpty(setting))
            {
                var exception = new ArgumentException("Automatic SMS Reminder: Basic Configuration is Missing");
                Utilities.LogError(exception, null);
            }
            else
            {
                try
                {
                    _Configuration = AutoReminderConfiguration.FACTORY_SETTINGS;
                    int iConfig = int.Parse(setting);
                    AutoReminderConfiguration config = (AutoReminderConfiguration)iConfig;
                    _Configuration = config;
                }
                catch (Exception ex)
                {
                    var exception = new ArgumentOutOfRangeException(
                        "Automatic SMS Reminder: Basic Configuration is Invalid", ex);
                    Utilities.LogError(exception, null);
                }
            }

            setting = Utilities.GetSetting(AutoReminderSettings.MESSAGE_TEMPLATE_ONE_REMINDER_PER_FACILITY);
            if (string.IsNullOrEmpty(setting))
            {
                var exception = new ArgumentException("Automatic SMS Reminder: One Reminder Per Facility Limit");
                Utilities.LogError(exception, null);
            }
            else
            {
                try
                {
                    _oneReminderPerFacilityLimit = AutoReminderSettings.DEFAULT_REPORT_NUMBER_BEFORE_SINGLE_REMINDER;
                    _oneReminderPerFacilityLimit = int.Parse(setting);
                }
                catch (Exception ex)
                {
                    var exception = new ArgumentException(
                        "Automatic SMS Reminder: One Reminder Per Facility Limit Invalid", ex);
                    Utilities.LogError(exception, null);
                }
            }

            _AllReportsMessageTemplate = AutoReminderSettings.DEFAULT_MESSAGE_TEMPLATE;
            setting = Utilities.GetSetting(AutoReminderSettings.MESSAGE_TEMPLATE);
            if (string.IsNullOrEmpty(setting))
            {
                var exception = new ArgumentException("Automatic SMS Reminder: Message Template is Missing");
                Utilities.LogError(exception, null);
            }
            else
            {
                _AllReportsMessageTemplate = setting;
            }

            _reportMessageTemplates = Utilities.GetAutoReminderReportMessageTemplates();
            DataRow[] rowsToRemove = _reportMessageTemplates.Select(DISABLED_REPORT_TEMPLATES_FILTER_EXPRESSION);
            foreach (DataRow r in rowsToRemove) _reportMessageTemplates.Rows.Remove(r);

            _facilityMissingReports = new Dictionary<string, Dictionary<int, string>>();

            setting = Utilities.GetSetting(AutoReminderSettings.AUTO_REMINDER_SETTING_SAVE_DATE);
            if (string.IsNullOrEmpty(setting))
            {
                DateTime settingsLastSaveDate = DateTime.Now;
                _settingsLastSaveDate = settingsLastSaveDate.ToString("s");
                Utilities.SetSetting(AutoReminderSettings.AUTO_REMINDER_SETTING_SAVE_DATE, _settingsLastSaveDate);
            }
            else
            {
                _settingsLastSaveDate = setting;
            }
        }

        public AutoReminder()
        {
            Initialize();
        }

        private object autoReminderLock = new object();
        public void SendAutomaticSMSReminder()
        {
            lock (autoReminderLock)
            {
                _totalMissingReports = 0;
                _totalSentMessages = 0;
                _timeTakenForSendingReminders = new TimeSpan(0);

                // Here we are checking if the settings have been changed since this instance 
                // was created. If so we re-initialize the instance with current settings
                // before trying to do any automatic sending!
                // The last settings date was saved in _settingsLastSaveDate and everytime
                // the setting is modified the database stored value of the last time the
                // setting was changed is stored.
                // We will compare this value against the one stored in this instance.
                // Everytime initialize is called, it store this database value to one of
                // its field.
                string setting = Utilities.GetSetting(AutoReminderSettings.AUTO_REMINDER_SETTING_SAVE_DATE);
                if (setting != _settingsLastSaveDate) Initialize();

                // Check to see if auto reminder is enabled
                bool enabled = IsAutomaticRemindersEnabled();
                if (enabled)
                {
                    DateTime previousMonth = DateTime.Now.AddMonths(AUTO_REMINDER_TARGET_MONTHS_BACK);
                    _targetMonth = previousMonth.Month;
                    _targetYear = previousMonth.Year;

                    _targetMonthText = Utilities.GetMonthText(_targetMonth, true);
                    _targetYearText = _targetYear.ToString();

                    setting = Utilities.GetSetting(AutoReminderSettings.AUTO_REMINDER_REMINDING_MONTH);
                    DateTime remindingDate;
                    if (!DateTime.TryParse(setting, out remindingDate)) remindingDate = previousMonth;
                    int remindingMonth = remindingDate.Month;
                    int remindingYear = remindingDate.Year;

                    int timesReminded = AutoReminderSettings.DEFAULT_TIMES_REMINDED_IN_MONTH;
                    if (remindingMonth == _targetMonth && remindingYear == _targetYear)
                    {
                        setting = Utilities.GetSetting(AutoReminderSettings.AUTO_REMINDER_TIMES_REMINDED_IN_MONTH);
                        if (!int.TryParse(setting, out timesReminded))
                        {
                            timesReminded = AutoReminderSettings.DEFAULT_TIMES_REMINDED_IN_MONTH;
                        }
                    }
                    else
                    {
                        // This is the different reminding month, so we reset iterations
                        string sRemindingMonth = DateTime.Now.AddMonths(AUTO_REMINDER_TARGET_MONTHS_BACK).ToString("s");
                        Utilities.SetSetting(AutoReminderSettings.AUTO_REMINDER_REMINDING_MONTH, sRemindingMonth);
                        Utilities.SetSetting(AutoReminderSettings.AUTO_REMINDER_TIMES_REMINDED_IN_MONTH, timesReminded);
                    }

                    // Check for exceptions that may prevent us from sending the reminders
                    bool okToContinue = CanSendAutoRemindersNow(timesReminded);
                    if (okToContinue)
                    {
                        DateTime startTime = DateTime.Now;
                        SendReminders();
                        DateTime endTime = DateTime.Now;
                        _timeTakenForSendingReminders = endTime - startTime;

                        timesReminded++;
                        string sRemindingMonth = DateTime.Now.AddMonths(AUTO_REMINDER_TARGET_MONTHS_BACK).ToString("s");
                        string sLastSendingDate = DateTime.Now.ToString("s");
                        Utilities.SetSetting(AutoReminderSettings.AUTO_REMINDER_REMINDING_MONTH, sRemindingMonth);
                        Utilities.SetSetting(AutoReminderSettings.AUTO_REMINDER_TIMES_REMINDED_IN_MONTH, timesReminded);
                        Utilities.SetSetting(AutoReminderSettings.AUTO_REMINDER_LAST_SEND_DATE, sLastSendingDate);
                    }
                }
            }
        }

        /// <summary>
        /// Check if auto reminders can be sent right now.
        /// The method takes into consideration the configurations
        /// </summary>
        /// <returns></returns>
        private bool CanSendAutoRemindersNow(int previousIteration)
        {
            bool okToContinue = true; // First assumption is everything is good to go!

            // Check the limit of iterations
            okToContinue = DoWeNeedToRepeatSendingRemindersInThisMonth(previousIteration);
            if (okToContinue)
            {
                // check the triggerding days before sending the reminders
                okToContinue = IsTriggeringDaysReached();
                if (okToContinue)
                {
                    // Check the holidays
                    okToContinue = CanSendRemindersInHolidays();

                    if (okToContinue)
                    {
                        // Check the week-ends
                        okToContinue = CanSendRemindersInWeekEnds();

                        if (okToContinue)
                        {
                            // Check the working hours
                            okToContinue = CanSendRemindersInWorkingHours();
                        }
                    }
                }
            }

            return okToContinue;
        }

        /// <summary>
        /// Check if it is possible to send reminders after several repetitions
        /// </summary>
        /// <param name="previousIterations">Count of repetition done so far</param>
        /// <returns></returns>
        private bool DoWeNeedToRepeatSendingRemindersInThisMonth(int previousIterations)
        {
            string setting = Utilities.GetSetting(AutoReminderSettings.AUTO_REMINDER_LAST_SEND_DATE);
            DateTime reminderLastSendDate;
            if (!DateTime.TryParse(setting, out reminderLastSendDate))
            {
                DateTime lastMonth = DateTime.Now.AddMonths(AUTO_REMINDER_TARGET_MONTHS_BACK);
                reminderLastSendDate = lastMonth;
                string sReminderLastSendDate = reminderLastSendDate.ToString("s");
                Utilities.SetSetting(AutoReminderSettings.AUTO_REMINDER_LAST_SEND_DATE, sReminderLastSendDate);
            }

            // Auto reminders should be sent out weekly
            if (AutoReminderConfiguration.REPETITION_PATTERN_WEEKLY ==
                (_Configuration & AutoReminderConfiguration.REPETITION_PATTERN_WEEKLY))
            {
                TimeSpan timeGap = DateTime.Now - reminderLastSendDate;
                if (timeGap.TotalDays < AUTO_REMINDER_WEEKLY_DAYS_GAP) return false;
            }

            // Auto reminders should be sent out monthly
            if (AutoReminderConfiguration.REPETITION_PATTERN_MONTHLY ==
                (_Configuration & AutoReminderConfiguration.REPETITION_PATTERN_MONTHLY))
            {
                TimeSpan timeGap = DateTime.Now - reminderLastSendDate;
                if (timeGap.TotalDays < AUTO_REMINDER_MONTHLY_DAYS_GAP) return false;
            }

            if (AutoReminderConfiguration.STOPPING_CONDITION_AFTER_TWO_ATTEMPTS ==
                (_Configuration & AutoReminderConfiguration.STOPPING_CONDITION_AFTER_TWO_ATTEMPTS))
            {
                if (previousIterations > UI_PRESET_TWO_REMINDING_ATTEMPTS) return false;
                else return true;
            }

            return true;
        }

        /// <summary>
        /// Check if it is possible to send reminders in working hours.
        /// The method also checks the current day against set of configurations.
        /// </summary>
        /// <returns></returns>
        private bool CanSendRemindersInWorkingHours()
        {
            string setting = Utilities.GetSetting(AutoReminderSettings.AUTO_REMINDER_BASIC_INFO);
            if (string.IsNullOrEmpty(setting))
            {
                var exception = new ArgumentException("Automatic SMS Reminder: Basic Configuration is Missing");
                Utilities.LogError(exception, null);
                return true;
            }

            try
            {
                int iConfig = int.Parse(setting);
                AutoReminderConfiguration config = (AutoReminderConfiguration)iConfig;
                if (AutoReminderConfiguration.SENDING_EXCEPTION_USE_WORKING_HOURS ==
                    (config & AutoReminderConfiguration.SENDING_EXCEPTION_USE_WORKING_HOURS))
                {
                    try
                    {
                        DateTime startWorkingHours;
                        DateTime endWorkingHours;

                        setting = Utilities.GetSetting(AutoReminderSettings.SENDING_EXCEPTION_START_WORKING);
                        if (string.IsNullOrEmpty(setting))
                        {
                            var exception = new ArgumentException("Automatic SMS Reminder: Start Working Hours is Missing");
                            Utilities.LogError(exception, null);
                            return true;
                        }
                        else
                        {
                            startWorkingHours = DateTime.Parse(setting);
                        }

                        setting = Utilities.GetSetting(AutoReminderSettings.SENDING_EXCEPTION_END_WORKING);
                        if (string.IsNullOrEmpty(setting))
                        {
                            var exception = new ArgumentException("Automatic SMS Reminder: End Working Hours is Missing");
                            Utilities.LogError(exception, null);
                            return true;
                        }
                        else
                        {
                            endWorkingHours = DateTime.Parse(setting);
                        }

                        if ((startWorkingHours.Year == endWorkingHours.Year)
                            && (startWorkingHours.Month == endWorkingHours.Month)
                            && (startWorkingHours.Day == endWorkingHours.Day))
                        {
                            TimeSpan startHours = startWorkingHours.TimeOfDay;
                            TimeSpan endHours = endWorkingHours.TimeOfDay;
                            TimeSpan now = DateTime.Now.TimeOfDay;

                            if (startHours <= now && now <= endHours) return true;
                            else return false;
                        }
                        else return true;
                    }
                    catch (Exception ex)
                    {
                        var exception = new ArgumentOutOfRangeException(
                            "Automatic SMS Reminder: Working Hours settings are Invalid", ex);
                        Utilities.LogError(exception, null);
                        return true;
                    }
                }
                else return true;
            }
            catch (Exception ex)
            {
                var exception = new ArgumentOutOfRangeException(
                    "Automatic SMS Reminder: Basic Configuration is Invalid", ex);
                Utilities.LogError(exception, null);
                return true;
            }
        }

        /// <summary>
        /// Check if it is possible to send reminders in week-ends.
        /// The method also checks the current day against set of configurations
        /// </summary>
        /// <returns>True if you can send reminders now otherwise false</returns>
        private bool CanSendRemindersInWeekEnds()
        {
            string setting = Utilities.GetSetting(AutoReminderSettings.AUTO_REMINDER_BASIC_INFO);
            if (string.IsNullOrEmpty(setting))
            {
                var exception = new ArgumentException("Automatic SMS Reminder: Basic Configuration is Missing");
                Utilities.LogError(exception, null);
                return true;
            }

            try
            {
                int iConfig = int.Parse(setting);
                AutoReminderConfiguration config = (AutoReminderConfiguration)iConfig;
                if (AutoReminderConfiguration.SENDING_EXCEPTION_EXCLUDE_WEEK_ENDS ==
                    (config & AutoReminderConfiguration.SENDING_EXCEPTION_EXCLUDE_WEEK_ENDS))
                {
                    // Is now week-end?
                    DayOfWeek today = DateTime.Now.DayOfWeek;

                    if (today == DayOfWeek.Sunday || today == DayOfWeek.Saturday) return false;
                    else return true;
                }
                else return true;
            }
            catch (Exception ex)
            {
                var exception = new ArgumentOutOfRangeException(
                    "Automatic SMS Reminder: Basic Configuration is Invalid", ex);
                Utilities.LogError(exception, null);
                return true;
            }
        }

        /// <summary>
        /// Check if it is possible to send reminders holidays.
        /// The methods also check the current day against set of configurations
        /// </summary>
        /// <returns></returns>
        private bool CanSendRemindersInHolidays()
        {
            string setting = Utilities.GetSetting(AutoReminderSettings.AUTO_REMINDER_BASIC_INFO);
            if (string.IsNullOrEmpty(setting))
            {
                var exception = new ArgumentException("Automatic SMS Reminder: Basic Configuration is Missing");
                Utilities.LogError(exception, null);
                return true;
            }

            try
            {
                int iConfig = int.Parse(setting);
                AutoReminderConfiguration config = (AutoReminderConfiguration)iConfig;
                if (AutoReminderConfiguration.SENDING_EXCEPTION_EXCLUDE_HOLIDAYS ==
                    (config & AutoReminderConfiguration.SENDING_EXCEPTION_EXCLUDE_HOLIDAYS))
                {
                    // Is today holiday?
                    int currentYear = DateTime.Now.Year;
                    //int currentMonth = DateTime.Now.Month;
                    //int currentDay = DateTime.Now.Day;
                    string today = String.Format("{0:MMM} {0:%d}", DateTime.Now);

                    DataTable dataTable = Utilities.GetHolidays(currentYear);
                    if (dataTable.Rows.Count == 0) return true;
                    else
                    {
                        foreach (DataRow r in dataTable.Rows)
                        {
                            string sHolidayDate = r["HolidayDate"].ToString();
                            if (today.Equals(sHolidayDate, StringComparison.OrdinalIgnoreCase)) return false;
                        }
                        return true;
                    }
                }
                else return true;
            }
            catch (Exception ex)
            {
                var exception = new ArgumentOutOfRangeException(
                    "Automatic SMS Reminder: Basic Configuration is Invalid", ex);
                Utilities.LogError(exception, null);
                return true;
            }
        }

        /// <summary>
        /// Check the number of days passed before automatic reminders can be sent.
        /// </summary>
        /// <returns>Return true if the days has been reached, otherwise false</returns>
        private bool IsTriggeringDaysReached()
        {
            string setting = Utilities.GetSetting(AutoReminderSettings.TRIGGER_DAYS_BEFORE_REMINDING);
            int triggeringDays = AutoReminderSettings.DEFAULT_DAYS_BEFORE_REMINDING;
            if (string.IsNullOrEmpty(setting))
            {
                Utilities.SetSetting(AutoReminderSettings.TRIGGER_DAYS_BEFORE_REMINDING,
                    AutoReminderSettings.DEFAULT_DAYS_BEFORE_REMINDING);
            }
            else
            {
                if (!int.TryParse(setting, out triggeringDays))
                {
                    triggeringDays = AutoReminderSettings.DEFAULT_DAYS_BEFORE_REMINDING;
                }
            }

            return (DateTime.Now.Day > triggeringDays);
        }

        /// <summary>
        /// Check to see if automatic SMS reminder is enabled
        /// </summary>
        /// <returns></returns>
        private bool IsAutomaticRemindersEnabled()
        {
            string setting = Utilities.GetSetting(AutoReminderSettings.ENABLE_AUTO_SMS_REMINDERS);
            bool enabled = AutoReminderSettings.DEFAULT_ENABLE_SMS_REMINDERS;
            if (string.IsNullOrEmpty(setting))
            {
                Utilities.SetSetting(AutoReminderSettings.ENABLE_AUTO_SMS_REMINDERS,
                    AutoReminderSettings.DEFAULT_ENABLE_SMS_REMINDERS);
            }
            else
            {
                if (!bool.TryParse(setting, out enabled))
                {
                    enabled = AutoReminderSettings.DEFAULT_ENABLE_SMS_REMINDERS;
                }
            }

            return enabled;
        }

        /// <summary>
        /// Go through each facility and sends the reminders
        /// </summary>
        private void SendReminders()
        {
            Dictionary<int, string> targetReports = new Dictionary<int, string>();
            for (int i = 0; i < _reportMessageTemplates.Rows.Count; i++)
            {
                DataRow row = _reportMessageTemplates.Rows[i];
                int reportId = int.Parse(row[REPORT_TEMPLATE_REPORT_ID_COLUMN_NAME].ToString());
                string reportShortCode = row[REPORT_TEMPLATE_REPORT_VERSION_COLUMN_NAME].ToString();
                targetReports[reportId] = reportShortCode;
            }

            DataTable targetFacilities = GetTargetFacilities();
            if (targetFacilities != null)
            {                
                foreach (DataRow r in targetFacilities.Rows)
                {
                    string facilityCode = r[AUTOREMINDER_FACILITY_CODE_COLUMN_NAME].ToString();
                    Dictionary<int, string> missingReorts = new Dictionary<int, string>();
                    _facilityMissingReports[facilityCode] = missingReorts;
                    
                    foreach (int reportId in targetReports.Keys)
                    {
                        DataTable dataTable = Utilities.GetMissingFacilityReport(facilityCode, 
                            reportId, _targetYear, _targetMonth, _targetYear, _targetMonth);
                        if (dataTable.Rows.Count != 0)
                        {
                            missingReorts[reportId] = targetReports[reportId];
                            _totalMissingReports++;
                        }
                    }
                    SendFacilityReminders(facilityCode);
                }
            }
        }

        /// <summary>
        /// Send Facility Reminders based on the presence of any missing report.
        /// </summary>
        /// <param name="facilityCode">Facility Code</param>
        private void SendFacilityReminders(string facilityCode)
        {
            Dictionary<int, string> missingReports = _facilityMissingReports[facilityCode];
            if (missingReports.Count > 0)
            {
                List<string> receivers = GetReminderReceivers(facilityCode, missingReports);
                if (receivers.Count > 0)
                {
                    if (AutoReminderConfiguration.MESSAGE_TEMPLATE_ONE_REMINDER_PER_FACILITY ==
                        (_Configuration & AutoReminderConfiguration.MESSAGE_TEMPLATE_ONE_REMINDER_PER_FACILITY))
                    {
                        if (missingReports.Count > _oneReminderPerFacilityLimit)
                        {
                            string reminderMessage = ReplacePlaceHolders(_AllReportsMessageTemplate, missingReports);
                            // Now we are sending just one reminder for all of the missing report.
                            foreach (string number in receivers) PutMessageInOutbox(reminderMessage, number);
                        }
                        else
                        {
                            SendRemindersForEachMissingReport(missingReports, receivers);
                        }
                    }
                    else
                    {
                        SendRemindersForEachMissingReport(missingReports, receivers);
                    }
                }
            }
        }

        /// <summary>
        /// Sends reminders for each of the missing report to the target receivers
        /// </summary>
        /// <param name="missingReports">List of missing reports</param>
        /// <param name="receivers">List of SMS reminders</param>
        private void SendRemindersForEachMissingReport(Dictionary<int, string> missingReports, 
            List<string> receivers)
        {
            foreach (int reportId in missingReports.Keys)
            {
                string reportTemplate = string.Empty;
                foreach (DataRow r in _reportMessageTemplates.Rows)
                {
                    int report = int.Parse(r[REPORT_TEMPLATE_REPORT_ID_COLUMN_NAME].ToString());
                    if (reportId == report)
                    {
                        reportTemplate = r[REPORT_TEMPLATE_MESSAGE_TEMPLATE_COLUMN_NAME].ToString();
                        break;
                    }
                }

                string missingReportShortCode = missingReports[reportId];
                string reminderMessage = ReplacePlaceHolders(reportTemplate, missingReportShortCode);
                foreach (string number in receivers) PutMessageInOutbox(reminderMessage, number);
            }
        }

        /// <summary>
        /// Puts the message in the outbox ready for sending.
        /// </summary>
        /// <param name="message">Message</param>
        /// <param name="receiverPhoneNumber">Receiver's phone number</param>
        private void PutMessageInOutbox(string message, string receiverPhoneNumber)
        {
            DbCommand command = GenericDataAccess.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = @"INSERT INTO SendSMSQueue([SenderNum],[TxtDetails])VALUES(@SenderNum,@TxtDetails)";

            DbParameter param = command.CreateParameter();
            param.ParameterName = "@SenderNum";
            param.DbType = DbType.String;
            param.Value = receiverPhoneNumber;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@TxtDetails";
            param.DbType = DbType.String;
            param.Value = message;
            command.Parameters.Add(param);
            GenericDataAccess.ExecuteNonQuery(command);

            _totalSentMessages++;
        }

        /// <summary>
        /// Replace message template placeholders with right values
        /// </summary>
        /// <param name="template">Message Template to modify</param>
        /// <param name="missingReports">List of missing Reports</param>
        /// <returns></returns>
        private string ReplacePlaceHolders(string template, Dictionary<int, string> missingReports)
        {
            string result = template;

            result = Regex.Replace(result, MESSAGE_TEMPLATE_PLACEHOLDER_MONTH, _targetMonthText);
            result = Regex.Replace(result, MESSAGE_TEMPLATE_PLACEHOLDER_YEAR, _targetYearText);

            if (missingReports != null && missingReports.Keys.Count > 0)
            {
                if (result.Contains(MESSAGE_TEMPLATE_PLACEHOLDER_REPORT))
                {
                    // Assumption here is to use the first missing report represent the missing report.
                    int report = missingReports.Keys.First();
                    string reportShortCode = missingReports[report];
                    result = Regex.Replace(result, MESSAGE_TEMPLATE_PLACEHOLDER_REPORT, reportShortCode);
                }

                if (result.Contains(MESSAGE_TEMPLATE_PLACEHOLDER_REPORTS))
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (int reportId in missingReports.Keys)
                    {
                        string reportShortCode = missingReports[reportId];
                        sb.Append(reportShortCode + ", ");
                    }

                    string replacement = sb.ToString();
                    replacement = replacement.Substring(0, replacement.Length - 2);
                    result = Regex.Replace(result, MESSAGE_TEMPLATE_PLACEHOLDER_REPORTS, replacement);
                }
            }

            return result;
        }

        /// <summary>
        /// Replace message template placeholders with right values
        /// </summary>
        /// <param name="template">Message Template to modify</param>
        /// <param name="missingReportShortCode">Missing Report Short Code</param>
        /// <returns></returns>
        private string ReplacePlaceHolders(string template, string missingReportShortCode)
        {
            string result = template;

            result = Regex.Replace(result, MESSAGE_TEMPLATE_PLACEHOLDER_MONTH, _targetMonthText);
            result = Regex.Replace(result, MESSAGE_TEMPLATE_PLACEHOLDER_YEAR, _targetYearText);
            result = Regex.Replace(result, MESSAGE_TEMPLATE_PLACEHOLDER_REPORT, missingReportShortCode);

            return result;
        }

        /// <summary>
        /// Get the list of user phone numbers which will receive the reminders
        /// </summary>
        /// <param name="facilityCode">Facility code</param>
        /// <param name="missingReports">List of missing reports associated with the facility</param>
        /// <returns></returns>
        private List<string> GetReminderReceivers(string facilityCode, Dictionary<int, string> missingReports)
        {
            #region Fetching Queries
            string FETCH_ALL_REGISTERED_USER = string.Format(
                @"SELECT CellNum FROM ApprovedSMSCells WHERE FacCode = '{0}' AND Deleted <> 1",
                facilityCode);

            string FETCH_LAST_REPORT_SENDER = @"SELECT ApprovedSMSCells.CellNum, MAX(ReceivedSMS.DateReceived) AS Ignore
                FROM ReceivedSMS INNER JOIN ApprovedSMSCells 
                ON ReceivedSMS.SenderNum = ApprovedSMSCells.CellNum
                AND ReceivedSMS.FacCode = ApprovedSMSCells.FacCode 
                WHERE (ReceivedSMS.FacCode = @FacilityCode) AND (ReceivedSMS.RepID = @ReportId) 
                GROUP BY ApprovedSMSCells.CellNum";

            string FETCH_SELECTED_USERS = string.Format(
                @"SELECT CellNum FROM ApprovedSMSCells WHERE FacCode = '{0}' AND Deleted <> 'TRUE'
                AND SendSMS = 'TRUE'", facilityCode);
            #endregion

            #region All Registered Users
            if (AutoReminderConfiguration.REMINDER_RECEIVERS_ALL_REGISTERED ==
                (_Configuration & AutoReminderConfiguration.REMINDER_RECEIVERS_ALL_REGISTERED))
            {
                DataTable dataTable = GenericDataAccess.RunSelectQuery(FETCH_ALL_REGISTERED_USER);
                var users = new List<string>();
                foreach (DataRow r in dataTable.Rows)
                {
                    string number = r["CellNum"].ToString();
                    users.Add(number);
                }
                return users;
            }
            #endregion

            #region Last Report Senders
            if (AutoReminderConfiguration.REMINDER_RECEIVERS_LAST_REPORT_SENDER ==
                (_Configuration & AutoReminderConfiguration.REMINDER_RECEIVERS_LAST_REPORT_SENDER))
            {    
                List<string> targetRecipients = new List<string>();                
                DbCommand command = GenericDataAccess.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = FETCH_LAST_REPORT_SENDER;

                foreach (int reportId in missingReports.Keys)
                {
                    command.Parameters.Clear();
                    DbParameter param = command.CreateParameter();
                    param.ParameterName = "@FacilityCode";
                    param.DbType = DbType.String;
                    param.Value = facilityCode;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@ReportId";
                    param.DbType = DbType.Int32;
                    param.Value = reportId;
                    command.Parameters.Add(param);

                    DataTable lastReportSenderDataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
                    foreach (DataRow r in lastReportSenderDataTable.Rows)
                    {
                        string number = r["CellNum"].ToString();
                        if (!targetRecipients.Contains(number)) targetRecipients.Add(number);
                    }
                }
                if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();

                if (targetRecipients.Count < MINIMUM_RECIPIENTS_FOR_LAST_REPORT_SENDER)
                {
                    DataTable dataTable = GenericDataAccess.RunSelectQuery(FETCH_SELECTED_USERS);
                    foreach (DataRow r in dataTable.Rows)
                    {
                        string number = r["CellNum"].ToString();
                        if (!targetRecipients.Contains(number)) targetRecipients.Add(number);
                    }
                }

                if (targetRecipients.Count < MINIMUM_RECIPIENTS_FOR_LAST_REPORT_SENDER)
                {
                    DataTable dataTable = GenericDataAccess.RunSelectQuery(FETCH_ALL_REGISTERED_USER);
                    foreach (DataRow r in dataTable.Rows)
                    {
                        string number = r["CellNum"].ToString();
                        if (!targetRecipients.Contains(number)) targetRecipients.Add(number);
                    }
                }

                return targetRecipients;
            }
            #endregion

            #region Selected Users
            if (AutoReminderConfiguration.REMINDER_RECEIVERS_SELECTED_USERS ==
                (_Configuration & AutoReminderConfiguration.REMINDER_RECEIVERS_SELECTED_USERS))
            {
                DataTable dataTable = GenericDataAccess.RunSelectQuery(FETCH_SELECTED_USERS);
                var users = new List<string>();
                foreach (DataRow r in dataTable.Rows)
                {
                    string number = r["CellNum"].ToString();
                    users.Add(number);
                }
                return users;
            }
            #endregion

            return new List<string>();
        }

        private DataTable GetTargetFacilities()
        {
            const string FETCH_FACILITIES_BY_REGIONS = @"SELECT Facilities.FacCode AS FacilityCode
                FROM Facilities INNER JOIN AutoReminderRegions 
                ON Facilities.Region = AutoReminderRegions.RegionId";

            const string FETCH_FACILITIES_BY_DISTRICTS = @"SELECT Facilities.FacCode AS FacilityCode
                FROM Facilities INNER JOIN AutoReminderDistricts 
                ON Facilities.District = AutoReminderDistricts.DistrictId";

            if (AutoReminderConfiguration.REMINDED_FACILITIES_BY_REGIONS ==
                (_Configuration & AutoReminderConfiguration.REMINDED_FACILITIES_BY_REGIONS))
            {
                DataTable allFacilities = GenericDataAccess.RunSelectQuery(FETCH_FACILITIES_BY_REGIONS);
                return allFacilities;
            }

            if (AutoReminderConfiguration.REMINDED_FACILITIES_BY_DISTRICTS ==
                (_Configuration & AutoReminderConfiguration.REMINDED_FACILITIES_BY_DISTRICTS))
            {
                DataTable allFacilities = GenericDataAccess.RunSelectQuery(FETCH_FACILITIES_BY_DISTRICTS);
                return allFacilities;
            }

            if (AutoReminderConfiguration.REMINDED_FACILITIES_BY_FACILITIES ==
                (_Configuration & AutoReminderConfiguration.REMINDED_FACILITIES_BY_FACILITIES))
            {
                DataTable selectedFacilities = Utilities.GetAutoReminderConfiguredFacilities();
                return selectedFacilities;
            }

            return null;
        }
    }
}
